import os
import sys
from datetime import datetime

import psycopg2


class DbOperate:
    def __init__(self, **dbinfo):
        self.db_info = dbinfo
        self.conn = None
        self.cur = None

    def conn_db(self):
        self.conn = psycopg2.connect(database=self.db_info['db_name'],
                                     user=self.db_info['db_user'],
                                     password=self.db_info['db_password'],
                                     host=self.db_info['db_host'],
                                     port=self.db_info['port'])
        self.conn.set_client_encoding('utf-8')
        self.cur = self.conn.cursor()
        print("connect successful")

    def exec_dml_sql(self, sql_cmd):
        print(sql_cmd)
        self.cur.execute(sql_cmd)
        self.conn.commit()
        print("dml operate successful")

    def exec_dql_sql(self, sql_cmd):
        self.cur.execute(sql_cmd)
        rows = self.cur.fetchall()
        print("select successful")
        return rows

    def close_conn(self):
        self.conn.close()
        print("close connect successful")


class DbBusiness:
    def __init__(self, db, feature, level):
        self.db = db
        self.feature = feature
        self.level = level
        self.tester = {}

    def db_operate(self, yat_dir, link_path):
        if self.feature == 'function':
            if self.level == '2':
                his_table_list = ['ReportApp_histier2funcinfo',
                                  'ReportApp_histier2funcmodel',
                                  'ReportApp_histier2funcos']
                table_list = ['ReportApp_tier2funcinfo',
                              'ReportApp_tier2funcmodel',
                              'ReportApp_tier2funcos']
                id_seq_list = ['ReportApp_tier2funcinfo_id_seq']
            else:
                his_table_list = ['ReportApp_histier3funcinfo',
                                  'ReportApp_histier3funcmodel',
                                  'ReportApp_histier3funcos']
                table_list = ['ReportApp_tier3funcinfo',
                              'ReportApp_tier3funcmodel',
                              'ReportApp_tier3funcos']
                id_seq_list = ['ReportApp_tier3funcinfo_id_seq']
        else:
            his_table_list = ['ReportApp_hisreliainfo',
                              'ReportApp_hisreliamodel',
                              'ReportApp_hisreliaos']
            table_list = ['ReportApp_reliainfo',
                          'ReportApp_reliamodel',
                          'ReportApp_reliaos']
            id_seq_list = ['ReportApp_reliainfo_id_seq']

        # 删除一个月前的数据
        try:
            del_his_cmd = "begin;\n"
            for t_name in his_table_list:
                del_his_cmd += f"delete from \"{t_name}\" as n " \
                    f"where n.start_time < to_timestamp(substring(" \
                    f"to_char(now(), 'yyyy-MM-DD hh24:MI:ss') " \
                    f"from 1 for 10), " \
                    f"'yyyy-MM-dd') - interval '30d';\n"
            del_his_cmd += 'end;'
            my_db.exec_dml_sql(del_his_cmd)
        except Exception as e:
            print("执行失败：" + str(e))
        print('删除一个月前的数据完成')

        # 处理历史数据，将之前的数据都转移到histroy表中
        try:
            trans_his_cmd = "begin;\n"
            trans_his_cmd += f'insert into "{his_table_list[0]}"(' \
                f'run_os, model_name, tc_name, run_result, start_time, ' \
                f'run_time, failed_reason, solution, problem_type, ' \
                f'issue_no, ' \
                f'develop_owner, test_owner, status, ip_name) ' \
                f'select run_os, model_name, tc_name, run_result, ' \
                f'start_time, ' \
                f'run_time, failed_reason, solution, ' \
                f'problem_type, issue_no, ' \
                f'develop_owner, test_owner, status, ip_name ' \
                f'from "{table_list[0]}";\n'
            trans_his_cmd += f'insert into "{his_table_list[1]}"' \
                f'(run_os, model_name, ' \
                f'total_num, pass_num, fail_num, block_num, start_time, ' \
                f'run_time, ip_name) ' \
                f'select run_os, model_name, total_num, ' \
                f'pass_num, fail_num, ' \
                f'block_num, start_time, run_time, ip_name ' \
                f'from "{table_list[1]}";\n'
            trans_his_cmd += f'insert into "{his_table_list[2]}"' \
                f'(run_os, total_num, ' \
                f'pass_num, fail_num, block_num, start_time, run_time) ' \
                f'select run_os, total_num, pass_num, ' \
                f'fail_num, block_num, ' \
                f'start_time, run_time ' \
                f'from "{table_list[2]}";\n'
            trans_his_cmd += "end;"
            my_db.exec_dml_sql(trans_his_cmd)
        except Exception as e:
            print("执行失败：" + str(e))
        print('处理历史数据，将之前的数据都转移到histroy表中完成')

        # 删除已转移数据
        try:
            del_cmd = "begin;\n"
            for t_name in table_list:
                del_cmd += f'delete from "{t_name}";\n'
            del_cmd += 'end;'
            my_db.exec_dml_sql(del_cmd)
        except Exception as e:
            print("执行失败：" + str(e))
        print('删除已转移数据完成')

        # 重置id(id对应的序列)
        try:
            reset_cmd = ''
            for id_seq in id_seq_list:
                reset_cmd += f"select setval('\"{id_seq}\"', 1, false);\n"
            my_db.exec_dql_sql(reset_cmd)
        except Exception as e:
            print("执行失败：" + str(e))
        print('重置id完成')

        info_value_list = []
        model_value_list = []
        f_dir = ''
        ip_name = ''
        link = ''
        # 遍历yat_log文件，写入数据库
        # noinspection PyBroadException
        try:
            for root, dirs, files in os.walk(yat_dir, topdown=True):
                for name in files:
                    if name.startswith('yat_'):
                        f_path = os.path.join(root, name)
                        for i_dir in f_path.split('/'):
                            if ('x86_CentOS' in i_dir) or (
                                    'x86_openEuler' in i_dir) or (
                                    'arm_openEuler' in i_dir):
                                f_dir = i_dir
                            elif str(i_dir).count('.') == 3:
                                ip_name = i_dir
                                console_path = os.path.join(link_path,
                                                            ip_name)
                                if os.path.exists(console_path):
                                    with open(console_path, 'r',
                                              encoding='utf-8',
                                              errors='ignore') as cf:
                                        link = cf.readline()
                        if feature_dir in f_dir:
                            run_os = '_'.join(f_dir.split('_')[1:-2:])
                            model_name = '_'.join(name.split('_')[1:-1:])
                            tester = self.tester.get(
                                model_name.split('_')[-1], '') + \
                                model_name.split('_')[-1]
                            model_fail_num = 0
                            model_block_num = 0
                            model_total_num = 0
                            model_pass_num = 0
                            model_start_time = datetime.now()
                            model_run_time = ''
                            with open(f_path, 'r', encoding='utf-8',
                                      errors='ignore') as f:
                                for line in f.readlines():
                                    orgin_line = line.strip().split(' ')
                                    if len(orgin_line) == 4:
                                        orgin_model_start_time = ' '.join(
                                            orgin_line[1:3:])
                                        model_start_time = datetime.strptime(
                                            orgin_model_start_time,
                                            '%Y-%m-%d %H:%M:%S')
                                    elif len(orgin_line) > 10:
                                        model_total_num = \
                                            orgin_line[3].split('/')[
                                                -1]
                                        model_pass_num = \
                                            orgin_line[3].split('/')[0]
                                        model_run_time = orgin_line[6].strip(
                                            'PT')
                                    elif 4 < len(orgin_line) <= 10:
                                        if orgin_line.count('er') > 0:
                                            model_fail_num += 1
                                        elif orgin_line.count('to') > 0:
                                            model_block_num += 1
                                        tc_name = orgin_line[6].split('/')[-1]
                                        run_result = orgin_line[-1]
                                        orgin_run_date = ' '.join(
                                            orgin_line[1:3:]).strip(
                                            '[').strip(']')
                                        start_time = datetime.strptime(
                                            orgin_run_date,
                                            '%Y-%m-%d %H:%M:%S')
                                        run_time = ' '.join(
                                            orgin_line[3:5:]).strip(
                                            '[').strip(']')

                                        info_value_list.append(
                                            f"('{run_os}', '{model_name}', "
                                            f"'{tc_name}', '{run_result}', "
                                            f"'{start_time}', '{run_time}', "
                                            f"'{ip_name}', '{link.strip()}', "
                                            f"'{tester}')")

                            model_value_list.append(
                                f"('{run_os}', '{model_name}', "
                                f"'{model_total_num}', '{model_pass_num}', "
                                f"'{model_fail_num}', '{model_block_num}',"
                                f"'{model_start_time}', "
                                f"interval '{model_run_time}', "
                                f"'{ip_name}')")

            info_cmd = f"begin;\n" \
                f"insert into \"{table_list[0]}\"(" \
                f"run_os, model_name, tc_name, run_result, start_time, " \
                f"run_time, ip_name, jenkins_link, test_owner) values "
            info_cmd += ',\n'.join(info_value_list)
            info_cmd += ";\nend;"
            my_db.exec_dml_sql(info_cmd)
            print('info表数据写入完成')

            sum_model_cmd = f"begin;\n" \
                f"insert into \"{table_list[1]}\"(" \
                f"run_os, model_name, total_num, pass_num, " \
                f"fail_num, block_num, start_time, run_time, " \
                f"ip_name) values "
            sum_model_cmd += ',\n'.join(model_value_list)
            sum_model_cmd += ";\nend;"
            my_db.exec_dml_sql(sum_model_cmd)
            print('model表数据写入完成')

            sum_os_cmd = "begin;\n"
            sum_os_cmd += f'insert into "{table_list[2]}"(' \
                f'run_os, total_num, pass_num, fail_num, block_num, ' \
                f'start_time, run_time) select run_os, ' \
                f'sum(total_num) as total_num, sum(pass_num) as pass_num, ' \
                f'sum(fail_num) as fail_num, sum(block_num) as block_num, ' \
                f'min(start_time) as start_time, max(run_time) as run_time ' \
                f'from (select run_os, sum(total_num) as total_num, ' \
                f'sum(pass_num) as pass_num, sum(fail_num) as fail_num, ' \
                f'sum(block_num) as block_num, ' \
                f'min(start_time) as start_time, ' \
                f'sum(run_time) as run_time ' \
                f'from "{table_list[1]}" ' \
                f'group by run_os, ip_name) ' \
                f'group by run_os;\n'
            sum_os_cmd += "end;"
            my_db.exec_dml_sql(sum_os_cmd)
            print('os表数据写入完成')
        except Exception as e:
            print("执行失败：" + str(e))
        print('表中写数据完成')


if __name__ == '__main__':
    Usage = "Usage:input as " \
            "'python3 db_operate.py feature_name tier run_result' "
    if len(sys.argv) < 3:
        print(Usage)
        exit(0)

    # 取值范围[function, reliability]
    feature_dir = sys.argv[1]
    tier = sys.argv[2]
    result_dir = sys.argv[3]
    console_link_path = '/data/jenkinsWS/console_link'

    db_info = {'db_name': '',
               'db_user': '',
               'db_password': '',
               'db_host': '',
               'port': 00000}
    my_db = DbOperate(**db_info)
    my_db.conn_db()
    my_business = DbBusiness(my_db, feature_dir, tier)
    # noinspection PyBroadException
    try:
        my_business.db_operate(result_dir, console_link_path)
    except Exception:
        print(Exception)
    finally:
        my_db.close_conn()
